/**
 * 查看站点基础数据统计信息
 */

const mysql = require('mysql2/promise');

const dbConf = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4'
};

async function showSiteStats() {
    const conn = await mysql.createConnection(dbConf);
    try {
        console.log('📊 站点基础数据统计信息:');
        
        // 总体统计
        const [total] = await conn.execute('SELECT COUNT(*) as total FROM site_base');
        console.log(`\n总计: ${total[0].total} 个站点`);
        
        // 按类型统计
        const [typeStats] = await conn.execute(`
            SELECT 
                type_name,
                type_id,
                COUNT(*) as count,
                MIN(area_id) as min_area_id,
                MAX(area_id) as max_area_id,
                AVG(ss) as avg_ss
            FROM site_base 
            GROUP BY type_id, type_name
            ORDER BY type_id
        `);
        
        console.log('\n按站点类型统计:');
        console.table(typeStats.map(row => ({
            '类型名称': row.type_name,
            '类型ID': row.type_id,
            '数量': row.count,
            '区域范围': `${row.min_area_id}-${row.max_area_id}`,
            '平均SS': Math.round(row.avg_ss || 0)
        })));
        
        // 按区域统计
        const [areaStats] = await conn.execute(`
            SELECT 
                area_id,
                COUNT(*) as site_count,
                GROUP_CONCAT(DISTINCT type_name ORDER BY type_name SEPARATOR ', ') as types,
                GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') as site_names
            FROM site_base 
            WHERE area_id IS NOT NULL
            GROUP BY area_id
            ORDER BY area_id
        `);
        
        console.log('\n按区域统计:');
        console.table(areaStats.map(row => ({
            '区域ID': row.area_id,
            '站点数量': row.site_count,
            '站点类型': row.types,
            '站点名称': row.site_names.length > 50 ? row.site_names.substring(0, 50) + '...' : row.site_names
        })));
        
        // 主城详情
        const [mainCities] = await conn.execute(`
            SELECT 
                name,
                area_id,
                ss,
                img_type,
                num
            FROM site_base 
            WHERE type_name = '主城'
            ORDER BY area_id, name
        `);
        
        console.log('\n🏛️ 主城详情:');
        console.table(mainCities.map(row => ({
            '主城名称': row.name,
            '区域ID': row.area_id,
            'SS值': row.ss,
            '图片类型': row.img_type,
            '数量': row.num
        })));
        
        // 副本地图详情
        const [dungeonMaps] = await conn.execute(`
            SELECT 
                name,
                area_id,
                ss,
                img_type,
                num
            FROM site_base 
            WHERE type_name = '副本地图'
            ORDER BY name
            LIMIT 15
        `);
        
        console.log('\n🗺️ 副本地图详情 (前15个):');
        console.table(dungeonMaps.map(row => ({
            '地图名称': row.name,
            '区域ID': row.area_id,
            'SS值': row.ss,
            '图片类型': row.img_type,
            '数量': row.num
        })));
        
        // 图片类型分布
        const [imgTypeStats] = await conn.execute(`
            SELECT 
                img_type,
                COUNT(*) as count,
                GROUP_CONCAT(DISTINCT type_name ORDER BY type_name SEPARATOR ', ') as types
            FROM site_base 
            WHERE img_type IS NOT NULL
            GROUP BY img_type
            ORDER BY img_type
        `);
        
        console.log('\n🖼️ 图片类型分布:');
        console.table(imgTypeStats.map(row => ({
            '图片类型': row.img_type,
            '数量': row.count,
            '站点类型': row.types
        })));
        
    } finally {
        await conn.end();
    }
}

if (require.main === module) {
    showSiteStats().catch((err) => {
        console.error('查询失败:', err.message);
        process.exit(1);
    });
}

module.exports = { showSiteStats };

